--DROP VIEW V_ADU_ASSIGN;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_ADU_ASSIGN
(
   ID,
   REGINFOID,
   CUSTNAME,
   DOCTYPE,
   DOCTYPENAME,
   CUSTNO,
   APPLYNO,
   APPDATE,
   LOANTYPE,
   SALESMANID,
   SALESMAN,
   DEPID,
   DEPNAME,
   BUSSTYPE,
   PRODUCTID,
   PRODUCTNAME,
   MEDIATORID,
   MEDIATOR,
   MEDIATORACCOUNT,
   MEDIATORTEL,
   SOURCE,
   ISSPECIAL,
   SPECID,
   CONTACT,
   CONTRACTTEL,
   CONTRACTTEL2,
   AMOUNT,
   LOANPHASE,
   LOANPHAESTYPE,
   RATE,
   MANAGEFEERATE,
   GENERALRATE,
   LOANFEE,
   STATUS,
   CANCELBY,
   CANCELDATE,
   CANCELREASON,
   BACKSTATUS,
   BACKDATE,
   BACKBY,
   BACKREASON,
   FLOWSTATUS,
   SYSTEMID,
   SITENO,
   SITENAME,
   SITEID,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   SUBMITBY,
   SUBMITDATE,
   DEALINGSTATUS,
   ASSIGNBY,
   ASSIGNTIME,
   FLOWUSER
)
AS
   SELECT pi."ID",
          pi."REGINFOID",
          pi."CUSTNAME",
          pi."DOCTYPE",
          pi."DOCTYPENAME",
          pi."CUSTNO",
          pi."APPLYNO",
          pi."APPDATE",
          pi."LOANTYPE",
          pi."SALESMANID",
          pi."SALESMAN",
          pi."DEPID",
          pi."DEPNAME",
          pi."BUSSTYPE",
          pi."PRODUCTID",
          pi."PRODUCTNAME",
          pi."MEDIATORID",
          pi."MEDIATOR",
          pi."MEDIATORACCOUNT",
          pi."MEDIATORTEL",
          pi."SOURCE",
          pi."ISSPECIAL",
          pi."SPECID",
          pi."CONTACT",
          pi."CONTRACTTEL",
          pi."CONTRACTTEL2",
          pi."AMOUNT",
          pi."LOANPHASE",
          pi."LOANPHAESTYPE",
          pi."RATE",
          pi."MANAGEFEERATE",
          pi."GENERALRATE",
          pi."LOANFEE",
          pi."STATUS",
          pi."CANCELBY",
          pi."CANCELDATE",
          pi."CANCELREASON",
          pi."BACKSTATUS",
          pi."BACKDATE",
          pi."BACKBY",
          pi."BACKREASON",
          pi."FLOWSTATUS",
          pi."SYSTEMID",
          pi."SITENO",
          pi."SITENAME",
          pi."SITEID",
          pi."SPAREFIELD1",
          pi."SPAREFIELD2",
          pi."SPAREFIELD3",
          pi."SUBMITBY",
          pi."SUBMITDATE",
          (CASE WHEN AA.ID > 0 THEN 1 ELSE 0 END) dealingstatus,
          AA.ASSIGNBY,
          AA.ASSIGNTIME,
          AA.FLOWUSER
     FROM tb_app_proinfo pi
          JOIN TB_ADU_ASSIGN aa ON PI.ID = AA.PROID(+) AND pi.status >= 21;


--DROP VIEW V_ADU_RISKCONTROL;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_ADU_RISKCONTROL
(
   ID,
   REGINFOID,
   CUSTNAME,
   DOCTYPE,
   DOCTYPENAME,
   CUSTNO,
   APPLYNO,
   APPDATE,
   LOANTYPE,
   SALESMANID,
   SALESMAN,
   DEPID,
   DEPNAME,
   BUSSTYPE,
   PRODUCTID,
   PRODUCTNAME,
   MEDIATORID,
   MEDIATOR,
   MEDIATORACCOUNT,
   MEDIATORTEL,
   SOURCE,
   ISSPECIAL,
   SPECID,
   CONTACT,
   CONTRACTTEL,
   CONTRACTTEL2,
   AMOUNT,
   LOANPHASE,
   LOANPHAESTYPE,
   RATE,
   MANAGEFEERATE,
   GENERALRATE,
   LOANFEE,
   STATUS,
   CANCELBY,
   CANCELDATE,
   CANCELREASON,
   BACKSTATUS,
   BACKDATE,
   BACKBY,
   BACKREASON,
   FLOWSTATUS,
   SYSTEMID,
   SITENO,
   SITENAME,
   SITEID,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   SUBMITBY,
   SUBMITDATE,
   DEALINGSTATUS,
   ASSIGNBY,
   ASSIGNTIME,
   FLOWUSER,
   RISKAUDITID,
   RISKAUDITBY,
   RISKAUDITTIME,
   RISKAUDITSTATUS
)
AS
   SELECT va."ID",
          va."REGINFOID",
          va."CUSTNAME",
          va."DOCTYPE",
          va."DOCTYPENAME",
          va."CUSTNO",
          va."APPLYNO",
          va."APPDATE",
          va."LOANTYPE",
          va."SALESMANID",
          va."SALESMAN",
          va."DEPID",
          va."DEPNAME",
          va."BUSSTYPE",
          va."PRODUCTID",
          va."PRODUCTNAME",
          va."MEDIATORID",
          va."MEDIATOR",
          va."MEDIATORACCOUNT",
          va."MEDIATORTEL",
          va."SOURCE",
          va."ISSPECIAL",
          va."SPECID",
          va."CONTACT",
          va."CONTRACTTEL",
          va."CONTRACTTEL2",
          va."AMOUNT",
          va."LOANPHASE",
          va."LOANPHAESTYPE",
          va."RATE",
          va."MANAGEFEERATE",
          va."GENERALRATE",
          va."LOANFEE",
          va."STATUS",
          va."CANCELBY",
          va."CANCELDATE",
          va."CANCELREASON",
          va."BACKSTATUS",
          va."BACKDATE",
          va."BACKBY",
          va."BACKREASON",
          va."FLOWSTATUS",
          va."SYSTEMID",
          va."SITENO",
          va."SITENAME",
          va."SITEID",
          va."SPAREFIELD1",
          va."SPAREFIELD2",
          va."SPAREFIELD3",
          va."SUBMITBY",
          va."SUBMITDATE",
          va."DEALINGSTATUS",
          va."ASSIGNBY",
          va."ASSIGNTIME",
          va."FLOWUSER",
          ar.id riskauditid,
          ar.AUDITBY riskauditby,
          ar.AUDITTIME riskaudittime,
          NVL (ar.STATUS, 0) riskauditstatus
     FROM V_ADU_ASSIGN va
          JOIN TB_ADU_RISKCONTROL ar
             ON va.id = AR.PROID(+) AND va.status >= 31;


--DROP VIEW V_APP_AUDIT;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_AUDIT
(
   ID,
   PROID,
   AUDITTYPE,
   AUDITBY,
   AUDITTIME,
   AUDITRESULT,
   AUDITCONTENT,
   APPLYNO,
   CUSTNAME,
   CUSTNO
)
AS
   SELECT DISTINCT aa."ID",
                   aa."PROID",
                   aa."AUDITTYPE",
                   aa."AUDITBY",
                   aa."AUDITTIME",
                   aa."AUDITRESULT",
                   aa."AUDITCONTENT",
                   ap.applyno,
                   ap.custname,
                   ap.custno
     FROM TB_APP_AUDIT aa LEFT JOIN tb_app_proinfo ap ON aa.proid = ap.id;


--DROP VIEW V_APP_REFREGINFO;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_REFREGINFO
(
   ID,
   REGINFOID,
   CUSTNAME,
   REFBY,
   REFBYNAME,
   APPDATE,
   OUTDEPID,
   OUTDEPNAME,
   OUTTIME,
   INDEPID,
   INDEPNAME,
   INTIME,
   SALESMANID,
   SALESMAN,
   DETAIL,
   STATUS,
   AUDITBY,
   AUDITTIME,
   BACKSTATUS,
   SPAREFIELD1,
   SPAREFIELD2,
   SPAREFIELD3,
   CUSTNO
)
AS
   SELECT DISTINCT r."ID",
                   r."REGINFOID",
                   r."CUSTNAME",
                   r."REFBY",
                   r."REFBYNAME",
                   r."APPDATE",
                   r."OUTDEPID",
                   r."OUTDEPNAME",
                   r."OUTTIME",
                   r."INDEPID",
                   r."INDEPNAME",
                   r."INTIME",
                   r."SALESMANID",
                   r."SALESMAN",
                   r."DETAIL",
                   r."STATUS",
                   r."AUDITBY",
                   r."AUDITTIME",
                   r."BACKSTATUS",
                   r."SPAREFIELD1",
                   r."SPAREFIELD2",
                   r."SPAREFIELD3",
                   ri.custno
     FROM TB_APP_REFREGINFO r
          LEFT JOIN tb_app_reginfo ri ON r.REGINFOID = ri.id;


--DROP VIEW V_APP_SPECIALINFO_ADU;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_APP_SPECIALINFO_ADU
(
   ID,
   LOANID,
   PROID,
   CONID,
   PID,
   SPECIALTYPE,
   SUBMITDEPART,
   SUBMITUSER,
   AUDITTIME,
   AUDITSTATUS,
   REMARK,
   TODEPART,
   TOUSER,
   REGINFOID,
   CUSTNAME,
   SALESMANID,
   SALESMAN,
   PRODUCTID,
   PRODUCTNAME,
   CREATEDATE
)
AS
   SELECT DISTINCT s."ID",
                   s."LOANID",
                   s."PROID",
                   s."CONID",
                   s."PID",
                   s."SPECIALTYPE",
                   s."SUBMITDEPART",
                   s."SUBMITUSER",
                   s."AUDITTIME",
                   s."AUDITSTATUS",
                   s."REMARK",
                   s."TODEPART",
                   s."TOUSER",
                   asp.reginfoid,
                   asp.custname,
                   asp.SALESMANID,
                   asp.SALESMAN,
                   asp.PRODUCTID,
                   asp.PRODUCTNAME,
                   asp.CREATEDATE
     FROM TB_ADU_SPECAIL s
          LEFT JOIN tb_app_specialinfo asp
             ON s.SPECIALTYPE = '5' AND s.pid = asp.id;


--DROP VIEW V_PRODUCT_METINFOSETDETAIL;

/* Formatted on 2013/11/29 17:30:09 (QP5 v5.227.12220.39724) */
CREATE OR REPLACE FORCE VIEW V_PRODUCT_METINFOSETDETAIL
(
   INFONAME,
   METINFOSETDETAILID,
   SORTNO,
   PRODUCTID
)
AS
   SELECT d.INFONAME,
          d.id metinfosetdetailid,
          d.sortno,
          p.id productid
     FROM tb_sys_metinfosetmain m,
          tb_sys_metinfosetdetail d,
          tb_sys_product p
    WHERE m.id = d.METIINFOID AND m.id = p.METINFOSETID;
